﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace CentroVentas.Clases.Funciones
{
    class Compra : Compras
    {
        #region Objetos
        DataBase db = DataBase.GetInstance();
        #endregion        

        #region Variables

        private int _id;
        private string _orden;
        // No usado
        // private DateTime _fechaPedido;
        private bool _estado;
        // private double _total;

        #endregion

        #region Constructores

        public Compra(int id)
        {
            _id = id;
        }

        public Compra(string orden)
        {
            try
            {
                DataBase db = DataBase.GetInstance();
                DataSet ds = new DataSet();

                db.dadap.SelectCommand = new SqlCommand("SELECT compra.id FROM compra WHERE orden = @orden", db.Conn);
                db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@orden", SqlDbType.VarChar)).Value = orden;
                db.dadap.Fill(ds, "idcompra");

                _id = Convert.ToInt32(ds.Tables["idcompra"].Rows[0].ItemArray[0]);
            }
            catch (Exception)
            {
                throw new Exception("No existe codigo de compra.");
            }
        }

        #endregion

        #region Propiedades

        public int Id
        {
            get { return _id; }
        }

        public int IdUsuario
        {
            get 
            {
                try
                {
                    DataSet ds = new DataSet();
                    db.dadap.SelectCommand = new SqlCommand("SELECT compra.usuario FROM compra WHERE compra.id = @id", db.Conn);
                    db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = Id;
                    db.dadap.Fill(ds, "IdUsuario");
                    return Convert.ToInt32(ds.Tables["IdUsuario"].Rows[0].ItemArray[0].ToString());
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public string NombreComprador
        {
            get
            {
                try
                {
                    DataSet ds = new DataSet();
                    db.dadap.SelectCommand = new SqlCommand("SELECT rut + ' - ' + nombreReal FROM usuario,compra WHERE usuario.id = compra.id AND compra.id = @id", db.Conn);
                    db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = Id;
                    db.dadap.Fill(ds, "Usuario");
                    return ds.Tables["Usuario"].Rows[0].ItemArray[0].ToString();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public string Orden
        {
            get
            {
                try
                {
                    DataSet ds = new DataSet();
                    db.dadap.SelectCommand = new SqlCommand("SELECT compra.orden FROM compra WHERE compra.id = @id", db.Conn);
                    db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = Id;
                    db.dadap.Fill(ds, "Orden");
                    return ds.Tables["Orden"].Rows[0].ItemArray[0].ToString();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public Proveedores Proveedor
        {
            get
            {
                try
                {
                    DataSet ds = new DataSet();
                    db.dadap.SelectCommand = new SqlCommand("SELECT compra.proveedor FROM compra WHERE compra.id = @id", db.Conn);
                    db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = Id;
                    db.dadap.Fill(ds, "proveedor");
                    return new Proveedores(Convert.ToInt32(ds.Tables["proveedor"].Rows[0].ItemArray[0].ToString()));
                }
                catch (Exception ex)
                {                    
                    throw new Exception(ex.Message);
                }
            }
        }


        public string Comentarios
        {
            get
            {
                try
                {
                    DataSet ds = new DataSet();
                    db.dadap.SelectCommand = new SqlCommand("SELECT compra.comentarios FROM compra WHERE compra.id = @id", db.Conn);
                    db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = Id;
                    db.dadap.Fill(ds, "comentarios");
                    return ds.Tables["comentarios"].Rows[0].ItemArray[0].ToString();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            set
            {
                try
                {
                    db.conectar();
                db.dadap.UpdateCommand = new SqlCommand("UPDATE compra SET orden = @value WHERE id = @id", db.Conn);
                db.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = Id;
                db.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@value", SqlDbType.VarChar)).Value = value;
                db.dadap.UpdateCommand.ExecuteNonQuery();
                db.desconectar();
                _orden = value;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public DateTime FechaPedido
        {
            get 
            {
                try
                {
                    DataSet ds = new DataSet();
                    db.dadap.SelectCommand = new SqlCommand("SELECT compra.fechapedido FROM compra WHERE compra.id = @id", db.Conn);
                    db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = Id;
                    db.dadap.Fill(ds, "fechapedido");
                    return Convert.ToDateTime(ds.Tables["fechapedido"].Rows[0].ItemArray[0].ToString());                    
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public bool Estado
        {
            get
            {
                try
                {
                    DataSet ds = new DataSet();
                    db.dadap.SelectCommand = new SqlCommand("SELECT compra.estado FROM compra WHERE compra.id = @id", db.Conn);
                    db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = Id;
                    db.dadap.Fill(ds, "estado");
                    if (Convert.ToInt32(ds.Tables["estado"].Rows[0].ItemArray[0].ToString()) == 1) return true;
                    return false;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            set
            {
                try
                {
                    ConfirmarCompra(Id);                    
                    _estado = value;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public double Total
        {
            get 
            {
                try
                {
                    
                    DataSet ds = new DataSet();
                    db.dadap.SelectCommand = new SqlCommand("SELECT SUM(precio.costo * precio.cantidad) FROM precio,detallecompra WHERE detallecompra.idprecio = precio.id AND detallecompra.idcompra = @id", db.Conn);
                    db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = Id;
                    db.dadap.Fill(ds, "total");

                    return Convert.ToDouble(ds.Tables[0].Rows[0].ItemArray[0].ToString());
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public int CantidadProductos
        {
            get
            {
                try
                {
                    DataSet ds = new DataSet();
                    db.dadap.SelectCommand = new SqlCommand("SELECT SUM(precio.cantidad) FROM precio,detallecompra WHERE detallecompra.idprecio = precio.id AND detallecompra.idcompra = @id", db.Conn);
                    db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = Id;
                    db.dadap.Fill(ds, "Cantidad");
                    return Convert.ToInt32(ds.Tables[0].Rows[0].ItemArray[0].ToString());
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }


        public DataSet DetalleCompras
        {
            get
            {
                try
                {
                    DataSet ds = new DataSet();
                    db.dadap.SelectCommand = new SqlCommand("SELECT producto.descripcion AS 'Descripcion', precio.costo AS 'Costo',precio.cantidad AS 'Cantidad' FROM precio,producto,detallecompra,compra WHERE precio.producto = producto.id AND producto.id = precio.producto AND precio.id = detallecompra.idprecio AND detallecompra.idcompra = compra.id AND compra.id = @id", db.Conn);
                    db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = Id;
                    db.dadap.Fill(ds, "Detalle");
                    return ds;
                }
                catch (Exception)
                {
                    throw new Exception("No se pudo cargar los datos");
                }
            }
        }
                

        #endregion

        #region Metodos no estaticos

        public DataSet getCompra()
        {
            try
            {
                DataSet ds = new DataSet();

                // Tabla Compra
                db.dadap.SelectCommand = new SqlCommand("SELECT compra.orden AS 'Orden',proveedor.rut AS 'RutProveedor',proveedor.nombre AS 'NomProveedor',proveedor.direccion1 AS 'DirProveedor', compra.comentarios AS 'Comentarios',compra.fechapedido AS 'FechaCompra' FROM compra,proveedor WHERE compra.orden = @orden AND compra.proveedor = proveedor.id", db.Conn);
                db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@orden", SqlDbType.VarChar)).Value = Orden;
                db.dadap.Fill(ds, "Compra");

                // Tabla Detalle
                db.dadap.SelectCommand = new SqlCommand("SELECT producto.descripcion AS 'Descripcion', precio.costo AS 'Costo',precio.cantidad AS 'Cantidad' FROM precio,producto,detallecompra,compra WHERE precio.producto = producto.id AND producto.id = precio.producto AND precio.id = detallecompra.idprecio AND detallecompra.idcompra = compra.id AND compra.orden = @orden", db.Conn);
                db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@orden", SqlDbType.VarChar)).Value = Orden;
                db.dadap.Fill(ds, "Detalle");
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public bool ConfirmarCompra(int _idC)
        {
            try
            {                
                db.dadap.UpdateCommand = new SqlCommand("UPDATE precio SET valido = 1 FROM precio,detallecompra WHERE precio.id = detallecompra.idprecio AND detallecompra.idcompra = @idC", db.Conn);
                db.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@idC", SqlDbType.Int)).Value = _idC;
                db.dadap.UpdateCommand.ExecuteNonQuery();

                db.dadap.UpdateCommand = new SqlCommand("UPDATE compra SET estado = 1,fechaPago = @fecha WHERE id = @idC", db.Conn);
                db.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@idC", SqlDbType.Int)).Value = _idC;
                db.dadap.UpdateCommand.Parameters.Add(new SqlParameter("@fechaPago", SqlDbType.DateTime2)).Value = DateTime.Now;
                db.dadap.UpdateCommand.ExecuteNonQuery();                           

                return true;
            }
            catch (Exception)
            {
                return false;
            }
                    
        }

        #endregion

        #region Metodos estaticos

        public static bool ExisteCompra(string orden)        
        {
            try
            {
                DataBase db = DataBase.GetInstance();
                DataSet ds = new DataSet();
                db.dadap.SelectCommand = new SqlCommand("SELECT COUNT (id) FROM compra WHERE orden = @orden", db.Conn);
                db.dadap.SelectCommand.Parameters.Add(new SqlParameter("@orden", SqlDbType.VarChar)).Value = orden;
                db.dadap.Fill(ds, "existe");
                if(Convert.ToInt32(ds.Tables["existe"].Rows[0].ItemArray[0].ToString()) == 1) return true;                
                return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        

        #endregion

    }
}
